﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using BioModel;

namespace BioDAL
{
    public class SaedeDAL : ISaedeDAL
    {
        #region Connection
        private SqlConnection sqlCn = null;

        public void OpenConnection(string connectionString)
        {
            Console.WriteLine("Connection Established");
            sqlCn = new SqlConnection();
            sqlCn.ConnectionString = connectionString;
            sqlCn.Open();
        }

        public void CloseConnection()
        {
            sqlCn.Close();
        }
        #endregion

        #region GetMaxID
        public int GetMaxSal(string kolonneNavn, int sal)
        {
            try
            {
                int mId = -1;
                string sql = string.Format("Select MAX({0}) as maxID from bioSaede where sal = {1}", kolonneNavn, sal);
                using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
                {
                    SqlDataReader dr = cmd.ExecuteReader();
                    while (dr.Read())
                    {
                        Console.WriteLine(String.Format("{0}", dr["maxID"]));
                        mId = (int)(dr["maxID"]);
                    }
                    dr.Close();
                }
                return mId;
            }
            catch (SqlException se2)
            {
                Console.WriteLine("!SQL fejl " + se2);
                return -1;
            }
        }
        #endregion

        #region Saede
        public void InsertSaede(int saedeNr, int raekkeNr, int forestillingsId)
        {
            try
            {
                string sql = string.Format("Insert into bioSaede" +
                     "(saedeNr, raekkeNr, saedeStatus, forestilling) Values" +
                     "('{0}', '{1}', '{2}', '{3}')", saedeNr, raekkeNr, 'l', forestillingsId);
                using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
                {
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception se)
            {
                Console.WriteLine("!SQL Insert Sal fejl: " + se);
            }
        }

        public void EditSaede(int saedeId, string saedeStatus)
        {
            string sql = string.Format("Update bioSaede Set saedeStatus = '{0}' Where saedeId = {1}"
                , saedeStatus, saedeId);

            using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
            {
                cmd.ExecuteNonQuery();
            }
        }

        private static Saede BuildSaede(SqlDataReader dr)
        {
            Saede saede = new Saede((int)(dr["saedeId"]), (int)(dr["saedeNr"]), (int)(dr["raekkeNr"]), (string)(dr["saedeStatus"]), (int)(dr["forestilling"]));
            return saede;
        }

        public List<Saede> AlleSaeder()
        {
            List<Saede> saedeListe = new List<Saede>();
            string sql = string.Format("Select * From bioSaede");
            using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
            {
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    saedeListe.Add(BuildSaede(dr));
                }
                dr.Close();
            }
            return saedeListe;
        }

        public List<Saede> AlleSaederFraForestilling(int forestillingsId)
        {
            List<Saede> saedeListe = new List<Saede>();
            string sql = string.Format("Select * From bioSaede where forestilling = {0}",forestillingsId);
            using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
            {
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    saedeListe.Add(BuildSaede(dr));
                }
                dr.Close();
            }
            return saedeListe;
        }

        public Saede GetEnSaede(int saedeId)
        {
            Saede sal = new Saede();
            string sql = string.Format("Select * from bioSaede where saedeId = {0}", saedeId);
            using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
            {
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    sal = (BuildSaede(dr));

                }
                dr.Close();
            }
            return sal;
        }

        public void SletEnSaede(int saedeId)
        {
            try
            {
                string sql = string.Format("DELETE FROM bioSaede Where saedeId = {0}"
                , saedeId);

            using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
            {
                cmd.ExecuteNonQuery();
            }
            }
            catch (Exception)
            {
                
                throw;
            }
        }
        #endregion
    }
}
